#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
============================================================================
    Desc: 监控数据库同步状态
    Author: zhoubangjun
    Date: 2023/6/5 
=============================================================================
"""
import json
import sys
import os
import logging

from datetime import datetime
import smtplib
from email.mime.text import MIMEText
from email.header import Header
from email.utils import formataddr

if sys.version_info.major < 3:
    reload(sys)
    sys.setdefaultencoding("utf-8")

base_dir = os.path.dirname(os.path.abspath(__file__))
mail_info_file = os.path.join(base_dir, '.mail_info.json')
db_info_file = os.path.join(base_dir, '.db_info.json')
log_file = os.path.join(base_dir, 'db_replication_monitor.log')

logger = logging.getLogger()
logger.setLevel(logging.DEBUG)
handler = logging.FileHandler(filename=log_file, mode='w', encoding='utf-8')
handler.setFormatter(logging.Formatter("%(asctime)s-%(name)s-%(levelname)s: %(message)s"))
logger.addHandler(handler)


class DbMonitor:
    def __init__(self):
        self.db_type_list = None
        self.db_type = None
        self.project = None
        self.host = None
        self.port = None
        self.username = None
        self.password = None

        with open(db_info_file) as f1:
            self.db_info = json.load(f1)
        self.db_type_list = list(self.db_info.keys())

    def init_check(self, db_type, project):
        self.db_type = db_type
        self.project = project
        self.db_project_info = self.db_info[db_type][project]
        self.username = self.db_project_info['username']
        self.password = self.db_project_info['password']
        self.host = self.db_project_info['host']
        self.port = self.db_project_info['port']

    def mysql_status(self, project=""):
        """
        检查mysql的SQL_Running、IO_Running线程运行状态是否正常，同步延迟是否超过60s
        """
        self.init_check(db_type='mysql', project=project)
        cmd = 'mysql -h{} -P{} -u{} -p{} -e"show slave status\G;" 2>/dev/null \
            | grep -E "_Running:|Seconds_Behind_Master"'.format(self.host, self.port, self.username, self.password)
        with os.popen(cmd) as f:
            result = f.readlines()
        try:
            status_list = [i.split(':')[-1].strip('\n').strip() for i in result]
            sql_io_running_status = ''.join(status_list[:2])
            seconds_behind_master = int(status_list[-1])

            if sql_io_running_status != 'YesYes' or seconds_behind_master > 60:
                logging.error('数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, result))
                return '数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, result)
            else:
                logging.info('数据库[{}-{}-{}]同步正常: {}'.format(self.project, self.db_type, self.host, result))
        except Exception as e:
            logging.error('数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, e))
            return '数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, e)

    def pg_status(self, project=""):
        """
        从库上检查pg_last_xact_replay_timestamp()的时间间隔是否正常
        """
        self.init_check(db_type='postgresql', project=project)
        cmd_1 = "export PGPASSWORD='{}'; psql -h{} -p{} -U{} -c'SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;' \
            | head -3 | tail -1".format(self.password, self.host, self.port, self.username)
        cmd_2 = "awk -F ':' '{print $3}' | awk -F'.' '{print $1}'"
        cmd = cmd_1 + '|' + cmd_2

        try:
            with os.popen(cmd) as f:
                result = f.read()
                    
            if int(result) > 60:
                logging.error('数据库[{}-{}-{}]同步延迟过高: {}'.format(self.project, self.db_type, self.host, result))
                return '数据库[{}-{}-{}]同步延迟过高: {}'.format(self.project, self.db_type, self.host, result)
            else:
                logging.info('数据库[{}-{}-{}]同步正常，同步延迟时间:{}'.format(
                    self.project, self.db_type, self.host, result))
        except Exception as e:
            logging.error('数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, e))
            return '数据库[{}-{}-{}]同步错误: {}'.format(self.project, self.db_type, self.host, e)


def check_all():
    check_all_result = []
    db_monitor = DbMonitor()
    for db_type in db_monitor.db_type_list:
        db_type_info = db_monitor.db_info[db_type]
        for project, _ in db_type_info.items():
            if db_type == 'mysql':
                result = db_monitor.mysql_status(project)
            elif db_type == 'postgresql':
                result = db_monitor.pg_status(project)
            if result:
                check_all_result.append(result)

    if check_all_result:
        return '\n\n'.join(check_all_result)


def send_mail(data):
    with open(mail_info_file) as f:
        mail_info = json.load(f)

    smtp_host = mail_info['smtp_host']
    username = mail_info['username']
    password = mail_info['password']
    to_addr = mail_info['to_addr']
    cc_addr = mail_info.get('cc_addr')
    today = datetime.strftime(datetime.today(), '%Y-%m-%d')
    email_topic = '数据库同步异常 - {}'.format(today).decode('utf-8')

    msg = MIMEText(data, 'plain', 'utf-8')
    msg['Subject'] = Header(email_topic, charset='utf-8')
    msg['From'] = formataddr(["paas", username])
    msg['To'] = ",".join(to_addr)
    if cc_addr:
        msg['Cc'] = ",".join(cc_addr)

    try:
        client = smtplib.SMTP(smtp_host, 25)
        client.starttls()
        client.login(username, password)
        client.sendmail(username, to_addr, msg.as_string())
        client.quit()
        logging.info('邮件发送成功！')
    except Exception as e:
        logging.error('邮件发送失败: {}'.format(e))


if __name__ == "__main__":
    data = check_all()
    if data:
        send_mail(data)
